package com.evaluationSystem.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.handler.inter.IReadHandler;
import com.evaluationSystem.entity.vo.AppraiseVo;
import com.evaluationSystem.enums.ContentTypeEnum;
import com.evaluationSystem.excel.AppraiseExcel;
import com.evaluationSystem.exception.EvaluationException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.springframework.beans.BeanUtils;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;

/**
 * excel工具类
 *
 * excel中xls和xlsx的区别是：
 * 1、文件格式不同。xls是一个特有的二进制格式，其核心结构是复合文档类型的结构，而xlsx的核心结构是XML类型的结构，采用的是基于 XML 的压缩方式，使其占用的空间更小。xlsx 中最后一个 x 的意义就在于此。
 * 2、版本不同。xls是excel2003及以前版本生成的文件格式，而xlsx是excel2007及以后版本生成的文件格式。
 * 3、兼容性不同。xlsx格式是向下兼容的，可兼容xls格式。
 *
 * @author Mark sunlightcs@gmail.com
 */
public class ExcelUtils {

    /**
     * Excel导入
     *
     * @param request       request
     * @param pojoClass     对象Class
     */
    public static List importExcel(HttpServletRequest request, Class<?> pojoClass) throws IOException {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        MultipartFile file = multipartRequest.getFile("file");
        if (file == null) {
            throw new EvaluationException("未找到上传的文件！");
        }
        ImportParams params = new ImportParams();
        params.setHeadRows(1);
        params.setNeedVerify(true); // 开启校验规则
        List targetList = null;

        try {
            System.out.println("正在读取文件: " + file.getOriginalFilename() + "，开始导入数据。");
            targetList = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (Exception e) {
            e.printStackTrace();
            throw new EvaluationException(e.getMessage());
        } finally {
            file.getInputStream().close();
        }

        return targetList;
    }

    /**
     * Excel大数据导入
     *
     * @param request       request
     * @param pojoClass     对象Class
     */
    public static Set importBigExcel(HttpServletRequest request, Class<?> pojoClass) throws IOException {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        MultipartFile file = multipartRequest.getFile("file");
        if (file == null) {
            throw new EvaluationException("未找到上传的文件！");
        }
        ImportParams params = new ImportParams();
        params.setHeadRows(1);
        params.setNeedVerify(true); // 开启校验规则
        Set targetList = new HashSet(); // 添加set集合过滤去重元素

        try {
            System.out.println("正在读取文件: " + file.getOriginalFilename() + "，开始导入数据。");
            ExcelImportUtil.importExcelBySax(file.getInputStream(), pojoClass, params, new IReadHandler() {
                @Override
                public void handler(Object o) {
                    targetList.add(o);
                }

                @Override
                public void doAfterAll() {

                }
            });
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            file.getInputStream().close();
        }

        return targetList;
    }

    /**
     * Excel导出
     *
     * @param response      response
     * @param fileName      文件名
     * @param list          数据List
     * @param pojoClass     对象Class
     */
    public static void exportExcel(HttpServletResponse response, String fileName, Collection<?> list,
                                   Class<?> pojoClass) throws IOException {

        // 设置导出格式为xlsx，默认xlsx
        ExportParams exportParams = new ExportParams();

        String suffix = "xls";
        if (StringUtils.isBlank(fileName)) {
            //当前日期
            fileName = DateUtil.formatDatetime(new Date());
        } else {
            if (fileName.indexOf(".") > 0) {
                suffix = fileName.substring(fileName.indexOf(".") + 1);
                fileName = fileName.substring(0, fileName.indexOf("."));
            }
        }
        if("xls".equals(suffix)) {
            exportParams.setType(ExcelType.HSSF);
        } else {
            exportParams.setType(ExcelType.XSSF);
        }

        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        response.setCharacterEncoding("UTF-8");
//        response.setHeader("content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel");
//        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("content-Type", ContentTypeEnum.forEach_ContentTypeEnum(suffix).getValue());
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + "." + suffix);
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
    }

    /**
     * Excel大数据导出
     *
     * @param list          数据List
     * @param pojoClass     对象Class
     */
    public static byte[] exportBigExcelByte(Collection<?> list, Class<?> pojoClass) throws IOException {
        Workbook workbook;

        // 设置导出单sheet页最大一百万行数据
        ExportParams exportParams = new ExportParams();
        exportParams.setMaxNum(1000000);
        exportParams.setType(ExcelType.XSSF);

        workbook = ExcelExportUtil.exportBigExcel(exportParams, pojoClass, (queryParams, num) -> {
            // 只导出一次，第二次返回null终止循环
            if (((int) queryParams) == num) {
                return null;
            }
            System.out.println("正在进行大数据量导出，条数: " + list.size());
            return Arrays.asList(list.toArray());
        }, 2);

        ByteArrayOutputStream bos = new ByteArrayOutputStream();

        workbook.write(bos);
        bos.close();

        byte[] bytes = bos.toByteArray();

        return bytes;
    }

    /**
     * Excel导出，先sourceList转换成List<targetClass>，再导出
     *
     * @param response      response
     * @param fileName      文件名
     * @param sourceList    原数据List
     * @param targetClass   目标对象Class
     */
    public static void exportExcelToTarget(HttpServletResponse response, String fileName, Collection<?> sourceList,
                                           Class<?> targetClass) throws Exception {
        List targetList = new ArrayList<>(sourceList.size());
        for (Object source : sourceList) {
            Object target = targetClass.newInstance();
            BeanUtils.copyProperties(source, target);
            targetList.add(target);
        }

        exportExcel(response, fileName, targetList, targetClass);
    }

    public static void exportExcelToTarget11(HttpServletResponse response, String fileName, Collection<?> sourceList,
                                           Class<?> targetClass) throws Exception {
        List targetList = new ArrayList<>(sourceList.size());
        for (Object source : sourceList) {
            Object target = targetClass.newInstance();

            if (source instanceof AppraiseVo && target instanceof AppraiseExcel) {
                AppraiseVo sourceVo = (AppraiseVo) source;
                AppraiseExcel targetExcel = (AppraiseExcel) target;

                targetExcel.setDeptName(sourceVo.getDeptName());
                targetExcel.setAppraiserName(sourceVo.getAppraiserName());
                targetExcel.setAppraiserPhone(sourceVo.getAppraiserPhone());
                targetExcel.setAssessedName(sourceVo.getAssessedName());
                targetExcel.setAppraiseCount(sourceVo.getAppraiseCount());
                targetExcel.setUnAppraiseCount(sourceVo.getUnAppraiseCount()); // 确保类型一致
                targetExcel.setAppraiseNotice(sourceVo.getAppraiseNotice());
                targetExcel.setAppraiseTime(sourceVo.getAppraiseTime());
            }

            targetList.add(target);
        }

        exportExcel(response, fileName, targetList, targetClass);
    }

    /**
     * Excel生成
     *
     * @param response      response
     * @param fileName      文件名
     */
    public static void mkdirExcel(HttpServletResponse response, String fileName,
                                  Workbook workbook) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            //当前日期
            fileName = DateUtil.formatDatetime(new Date());
        }

        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
    }

    /**
     * XSSF
     * excel添加下拉数据校验
     *
     * @param workbook   哪个 sheet 页添加校验
     * @param dataSource 数据源数组
     * @param col        第几列校验（0开始）
     * @return
     */
    public static void createXssfSelected(Workbook workbook, String[] dataSource, int col) {
        Sheet sheet = workbook.getSheetAt(0);
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, col, col);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(dataSource);
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        //处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        dataValidation.setEmptyCellAllowed(true);
        dataValidation.setShowPromptBox(true);
        dataValidation.createPromptBox("提示", "只能选择下拉框里面的数据");
        sheet.addValidationData(dataValidation);
    }

}
